﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Camelot.SharePointConnector.Data;
using Camelot.SharePointIntegration.Client;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Utilities;


namespace CamelotSQLCommander.CamelotSQLCommander
{
    public partial class CamelotSQLCommanderUserControl : UserControl
    {
        public string ConnectionString { get; set; }

        public CamelotSQLCommander CamelotSqlCommander { get; set; }

        protected void Page_Load(object sender, EventArgs e)
        {
            string site = ddlSites.SelectedValue;

            ConnectionString = string.Concat("Server=", CamelotSqlCommander.ServerAddress,
                                                 ";Database=", site,
                                                 ";Domain=", CamelotSqlCommander.UserDomain,
                                                 ";User=", CamelotSqlCommander.UserName,
                                                 ";Password=", CamelotSqlCommander.UserPassword,
                                                 ";Authentication=", CamelotSqlCommander.Authentication,
                                                 ";TimeOut=", CamelotSqlCommander.Timeout,
                                                 ";decodename=true;DefaultLimit=", CamelotSqlCommander.DefaultLimit,
                                                 ";MaxReceivedMessageSize=52428800");


            //pnlDebug.Controls.Add(new LiteralControl(ConnectionString));

            //Anything below should not be run on postback
            if (IsPostBack)
                return;

            ddlSites.DataSource = GetAllSites();
            ddlSites.DataValueField = "relativeUrl";
            ddlSites.DataTextField = "name";
            ddlSites.DataBind();
        }

        /// <summary>
        /// Get all sites in the current site collection
        /// </summary>
        /// <returns></returns>
        private DataTable GetAllSites()
        {
            try
            {
                SPSite oSiteCollection = SPContext.Current.Site;
                SPWebCollection collWebs = oSiteCollection.AllWebs;

                DataTable dt = new DataTable();
                DataRow row;
                DataColumn column;

                column = new DataColumn();
                column.DataType = System.Type.GetType("System.String");
                column.ColumnName = "name";
                column.ReadOnly = true;
                column.Unique = false;
                dt.Columns.Add(column);

                column = new DataColumn();
                column.DataType = System.Type.GetType("System.String");
                column.ColumnName = "relativeUrl";
                column.ReadOnly = true;
                column.Unique = false;
                dt.Columns.Add(column);

                //return collWebs;

                foreach (SPWeb oWebsite in collWebs)
                {
                    row = dt.NewRow();
                    row["name"] = (oWebsite.ServerRelativeUrl == "/") ? "Root" : SPEncode.HtmlEncode(oWebsite.Name);
                    row["relativeUrl"] = oWebsite.ServerRelativeUrl;
                    dt.Rows.Add(row);
                    oWebsite.Dispose();
                }
                return dt;
            }
            catch (Exception a)
            {
                pnlDebug.Controls.Add(new LiteralControl(a.Message));
            }
            return null;
        }

        protected void QueryClick(object sender, EventArgs e)
        {
            try
            {
                string query = txtQuery.Text;

                if (string.IsNullOrEmpty(query))
                    query = "show tables";

                CommandType? queryType = QueryType(query);
                if (queryType == null)
                {
                    pnlDebug.Controls.Add(new LiteralControl("<p>You must state a correct query</p>"));
                    return;
                }

                var outputType = GetOutputType();

                switch (queryType)
                {
                    case CommandType.SELECT:
                    case CommandType.SHOW:

                        if (outputType.Equals(OutputType.WINDOW))
                        {
                            gwResult.DataSource = Camelot.SharePointConnector.Data.Helper.ExecuteDataTable(query, ConnectionString);
                            gwResult.DataBind();
                        }

                        if (outputType.Equals(OutputType.CAMELOTXML))
                        {
                            using (var connection = new SharePointConnection(ConnectionString))
                            {
                                var listData = Camelot.SharePointIntegration.ListDataUtility.CreateListData(query, connection);
                                var data = System.Text.Encoding.UTF8.GetBytes(CamelotXml.CreateXml(listData));
                                Page.Response.Clear();
                                Page.Response.ContentType = "application/data";
                                Page.Response.AddHeader("content-disposition", "attachment; filename=CamelotXMLPacket.xml");
                                Page.Response.BinaryWrite(data);
                                Page.Response.End();
                            }
                        }

                        if (outputType.Equals(OutputType.CAMELOTXMLCOMPRESS))
                        {
                            using (var connection = new SharePointConnection(ConnectionString))
                            {
                                var listData = Camelot.SharePointIntegration.ListDataUtility.CreateListData(query, connection);
                                var data = System.Text.Encoding.UTF8.GetBytes(CamelotXml.CreateXml(listData));
                                var dataGz = Helpers.gzdeflate(data);
                                var dataExport = Helpers.base64_encode(dataGz);
                                Page.Response.Clear();
                                Page.Response.ContentType = "application/data";
                                Page.Response.AddHeader("content-disposition", "attachment; filename=CamelotXMLPacket.cxp");
                                Page.Response.Write(dataExport);
                                Page.Response.End();
                            }
                        }
                        break;

                    case CommandType.INSERT:
                    case CommandType.UPDATE:
                    case CommandType.DELETE:
                    case CommandType.CALL:
                        if (outputType.Equals(OutputType.WINDOW))
                        {
                            try
                            {
                                Camelot.SharePointConnector.Data.Helper.ExecuteNonQuery(query, ConnectionString);
                                pnlDebug.Controls.Add(new LiteralControl("<p>Query successfully executed!</p>"));
                            }
                            catch (Exception ex)
                            {
                                pnlDebug.Controls.Add(new LiteralControl(string.Concat("<p>", ex.Message, "</p>")));
                            }
                        }
                        else
                        {
                            pnlDebug.Controls.Add(new LiteralControl(string.Format("<p>You can only export the result of SELECT and SHOW queries, you tried to execute a {0} query</p>", queryType)));
                        }
                        break;
                }
            }
            catch (Exception a)
            {
                pnlDebug.Controls.Add(new LiteralControl(string.Concat("<p>", a.Message, "</p>")));
            }
        }

        private OutputType GetOutputType()
        {
            if (!string.IsNullOrEmpty(ddlOutput.SelectedValue))
                return (OutputType)Enum.Parse(typeof(OutputType), ddlOutput.SelectedValue);

            return OutputType.WINDOW;
        }
        
        private static CommandType? QueryType(string sharePointQuery)
        {
            var m = sharePointQuery.Trim().Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);

            if (m.Length <= 0)
                return null;

            var command = m[0].ToUpper();

            // Allowed commands
            var commands = new List<string> { "SHOW", "SELECT", "INSERT", "UPDATE", "DELETE", "CALL", "USE" };
            if (commands.Exists(x => x.Equals(command)))
            {
                // Make a queryable out of the enum values
                IQueryable<CommandType> commandTypes = Enum.GetValues(typeof(CommandType)).AsQueryable().Cast<CommandType>();

                // Convert the inserted string to a CommandType
                CommandType commandToCompare = (CommandType)Enum.Parse(typeof(CommandType), commands.Single(x => x.Equals(command)));

                // return the outcome, possibly null if no match
                return commandTypes.Single(x => x.Equals(commandToCompare));
            }

            return null;
        }
        /**/

    }
}
